package com.sys.utils;

import com.alibaba.fastjson.JSON;
import com.bhudy.annotation.ManyToOne;
import com.bhudy.annotation.OneToMany;
import com.bhudy.annotation.SkipAttribute;
import com.bhudy.exception.SysException;
import com.bhudy.exception.SysExceptionCode;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author com.bhudy
 * @Description Excel处理工具类
 * @Date 2019-10-30 23:09:37
 */
public final class ExcelUtil {

    private ExcelUtil() {
    }

    /**
     * 导出Excel
     *
     * @param list 内容
     * @return
     */
    public static <T> void getHSSFWorkbook(List<T> list) {
        getHSSFWorkbook(null, list);
    }

    public static <T> void getHSSFWorkbook(String[] title, List<T> list) {
        getHSSFWorkbook("", title, list);
    }

    public static <T> void getHSSFWorkbook(String sheetName, String[] title, List<T> list) {
        //方法 一
        List<Map<String, Object>> dataList = (List<Map<String, Object>>) JSON.toJSON(list);
        Field[] fields = list.get(0).getClass().getDeclaredFields();
        String[] colName = new String[fields.length];

        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            field.setAccessible(true);
            if (!field.isAnnotationPresent(ManyToOne.class) && !field.isAnnotationPresent(OneToMany.class) && !field.isAnnotationPresent(SkipAttribute.class)) {
                colName[i] = fields[i].getName();
            }
        }

        if (title == null) title = colName;

        getHSSFWorkbook(sheetName, title, colName, dataList);
    }

    /**
     * 导出Excel
     *
     * @param sheetName 文件名称
     * @param title     标题
     * @param colName   字段名字
     * @param list      内容
     */
    public static void getHSSFWorkbook(String sheetName, String[] title, String[] colName, List<Map<String, Object>> list) {
        sheetName += System.currentTimeMillis();

        HSSFWorkbook wb = null;
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            Map<String, Object> dataMap = list.get(i);

            for (int j = 0; j < colName.length; j++) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(dataMap.get(colName[j]) == null ? "" : dataMap.get(colName[j]).toString());
            }
        }


        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        sheetName += ".xls";
        try {
            sheetName = new String(sheetName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + sheetName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");

            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException ex) {
            throw new SysException(ex.getMessage());
        }
    }

    /**
     * <a class="btn btn-social-icon btn-info import_btn" data-toggle="tooltip" title='导入'><i class="fa fa-download"></i></a>
     * <form enctype="multipart/form-data" id="batchUpload" method="post" class="form-horizontal">
     * <input type="file" name="file" style="display:none;" id="input_file_add" accept=".xlsx,.xls">
     * </form>
     * <p>
     * // 导入按钮
     * $('.import_btn').click(function() {
     * $('#input_file_add').click()
     * })
     * <p>
     * // 导入上传
     * $('#input_file_add').change(function() {
     * const file_obj = $('#input_file_add').val()
     * <p>
     * if(file_obj === '') {
     * return false
     * } else if(file_obj.lastIndexOf('.xls') < 0) {
     * return common.showTips('只能上传Excel文件')
     * }
     * <p>
     * const formData = new FormData($('#batchUpload')[0])
     * $.ajax({
     * url: '/api/v1/universDepartmentsImport.do',
     * type: 'POST',
     * data: formData,
     * dataType: "json",
     * cache: false,
     * processData: false,
     * contentType: false,
     * success: function(data) {
     * common.showTips(`总数：${data.data.createIdCount}&nbsp;&nbsp;&nbsp;&nbsp;成功：${data.data.createCount}&nbsp;&nbsp;&nbsp;&nbsp;失败：${data.data.errorCount}&nbsp;&nbsp;&nbsp;&nbsp;更新：${data.data.updateCount}`)
     * $('#input_file_add').val('')
     * getTableData()
     * }
     * })
     * })
     * <p>
     * <p>
     *
     * @param file
     * @param clazz
     * @param <T>
     * @return //@RequestParam(value = "file", required = false) MultipartFile file
     * List<Map<String, Object>> dataMapList = ExcelUtil.importExcel(file, UniversChargeterms.class);
     * int updateCount = 0;
     * int createCount = 0;
     * int errorCount = 0;
     * if (dataMapList != null && dataMapList.size() != 0) {
     * for (Map<String, Object> dataMap : dataMapList) {
     * try {
     * UniversChargeterms universChargeterms = JSONObject.parseObject(JSONObject.toJSONString(dataMap), UniversChargeterms.class);
     * if (universChargeterms.getId() != 0 && universChargetermsService.load(UniversChargeterms.class, universChargeterms.getId()) != null) {
     * updateCount += universChargetermsService.update(dataMap, universChargeterms);
     * } else {
     * createCount += universChargetermsService.create(universChargeterms);
     * }
     * } catch (Exception e) {
     * errorCount++;
     * }
     * }
     * }
     * <p>
     * Map<String, Integer> resultMap = new HashMaphMap<>();
     * resultMap.put("createIdCount", dataMapList.size());
     * resultMap.put("updateCount", updateCount);
     * resultMap.put("createCount", createCount);
     * resultMap.put("errorCount", errorCount);
     */
    public static <T> List<Map<String, Object>> importExcel(MultipartFile file, Class<T> clazz) {
        Map<String, String> titleMap = new HashMap<>();
        Field[] fields = clazz.getDeclaredFields();

        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            field.setAccessible(true);
            titleMap.put(field.getName(), field.getName());
        }

        return importExcel(file, titleMap);
    }

    public static List<Map<String, Object>> importExcel(MultipartFile file, Map<String, String> titleMap) {
        //需要解析的Excel文件
        try {
            List<Map<String, Object>> dataMapList = new ArrayList<>();
            //获取工作簿
            FileInputStream fs = (FileInputStream) file.getInputStream();// FileUtils.openInputStream(file);

            String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
            Workbook workbook;
            Sheet sheet;
            if (suffix.equals("xls")) {
                workbook = new HSSFWorkbook(fs);
            } else if (suffix.equals("xlsx")) {
                workbook = new XSSFWorkbook(fs);
            } else {
                throw new SysException(SysExceptionCode.CODE_40045);
            }

            sheet = workbook.getSheetAt(0);


            //获取Sheet的第一个行号和最后一个行号
            int last = sheet.getLastRowNum();
            int first = sheet.getFirstRowNum();

            Map<Integer, String> colNameMap = new HashMap<>();

            //遍历获取单元格里的信息
            for (int i = first; i <= last; i++) {
                Row row = sheet.getRow(i);
                int firstCellNum = row.getFirstCellNum();//获取所在行的第一个行号
                int lastCellNum = row.getLastCellNum();//获取所在行的最后一个行号
                Map<String, Object> dataMap = new HashMap<>();
                for (int j = firstCellNum; j < lastCellNum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) continue;
                    Object value;
                    try {
                        if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                //用于转化为日期格式
                                Date d = cell.getDateCellValue();
                                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:write");
                            /*value = formater.format(d);*/
                                value = cell.getDateCellValue();
                            } else {
                                // 用于格式化数字，只保留数字的整数部分
                                DecimalFormat df = new DecimalFormat("########");
                                value = df.format(cell.getNumericCellValue());
                            }
                        } else {
                            cell.setCellType(CellType.STRING);
                            value = cell.getStringCellValue().toString();
                        }
                    } catch (Exception e) {
                        cell.setCellType(CellType.STRING);
                        value = cell.getStringCellValue().toString();
                    }

                    if (i == 0) {
                        colNameMap.put(j, titleMap.get(value));
                    } else {
                        if (colNameMap.containsKey(j) && colNameMap.get(j) != null) {
                            dataMap.put(colNameMap.get(j), value);
                        }
                    }
                }
                if (dataMap != null && dataMap.size() != 0) {
                    dataMapList.add(dataMap);
                }
            }
            return dataMapList;
        } catch (IOException e) {
            throw new SysException(e.getMessage());
        }
    }


}